from decimal import Decimal

import requests
import MySQLdb
from MySQLdb import OperationalError
import time

# 配置数据库
db_config = {
    'host': 'localhost',
    'user': 'root',  # 替换为你的用户名
    'password': '',  # 替换为你的密码
    'database': 'bilibili',  # 替换为你的数据库名
    'port': 3307,
    'autocommit': True  # 自动提交事务
}

url = "https://datacenter-web.eastmoney.com/api/data/v1/get"
params = {
    "sortColumns": "NOTICE_DATE,SUM,RECEIVE_START_DATE,SECURITY_CODE",
    "sortTypes": "-1,-1,-1,1",
    "pageSize": "50",
    "pageNumber": "1",
    "reportName": "RPT_ORG_SURVEYNEW",
    "columns": "SECUCODE,SECURITY_CODE,SECURITY_NAME_ABBR,NOTICE_DATE,RECEIVE_START_DATE,RECEIVE_PLACE,RECEIVE_WAY_EXPLAIN,RECEPTIONIST,SUM",
    "quoteColumns": "f2~01~SECURITY_CODE~CLOSE_PRICE,f3~01~SECURITY_CODE~CHANGE_RATE",
    "quoteType": "0",
    "source": "WEB",
    "client": "WEB",
    "filter": "(NUMBERNEW=\"1\")(IS_SOURCE=\"1\")(RECEIVE_START_DATE>'2022-06-16')"
}

headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/137.0.0.0 Safari/537.36 Edg/137.0.0.0"
}

try:
    # 建立数据库连接
    conn = MySQLdb.connect(**db_config)
    cursor = conn.cursor()
    print("数据库连接成功")
    max_batch_size = 10000
    cache_data_list = []
    # 数据的爬取
    for page in range(1, 474):
        params["pageNumber"] = page
        response = requests.get(url, headers=headers, params=params)
        if response.status_code == 200:
            items = response.json().get("result").get("data")
            # 处理数据并验证
            for item in items:
                # 提前初始化为默认值
                price_value = None
                price_change_value = ""

                code = str(item.get("SECURITY_CODE", ""))
                name = item.get("SECURITY_NAME_ABBR", "")

                price = item.get("CLOSE_PRICE", "")
                price_change = item.get("CHANGE_RATE", "")

                # 处理price类型
                if price != "-":
                    price_value = Decimal(str(price))
                else:
                    price_value = None

                if price_change != "-":

                    price_change_value = f"{float(price_change):.2f}%"
                else:
                    price_change_value = ""
                # 添加到缓存列表中
                cache_data_list.append([code, name, price_value, price_change_value])

        print(f"第 {page} 页数据处理完成，累计 {len(cache_data_list)} 条")

    # 批量插入数据库
    if cache_data_list:
        sql = """
               INSERT INTO tb_gupiao (code, name, price, price_change)
               VALUES (%s, %s, %s, %s)
               """
        cursor.executemany(sql, cache_data_list)
        conn.commit()
        print(f"成功插入 {len(cache_data_list)} 条数据")
    else:
        print(f"没有可插入的数据")
except OperationalError as e:
    print(f"数据库操作错误: {str(e)}")
except Exception as e:
    print(f"发生未知错误: {str(e)}")
finally:
    # 关闭资源
    if conn:
        cursor.close()
        conn.close()
        print("数据库连接已关闭")
